5  Stem-table mapping

The purpose of using an intermediate stem table between the source data and the clinical OMOP tables is to serve as an efficient, data-driven routing mechanism that allows tailored processing of the source data, and allows different source tables from data that will, eventually, land in the same clinical table. The table-level mechanism is illustrated in figure XXX.

The stem table relies on two key tables to do the routing: concept_lookup and concept_lookup_stem. These auxiliary tables are designed so as to enable using the same ETL pipeline for multiple sources (e.g., different ICUs from different hospitals).

Table-level routing mechanism of the stem table

We, essentially, deploy two types of source->stem mechanisms: one tailored for drug data and a simple one (for the rest).

{align=“center”, background=“white”}

5.0.1 Reading from prescriptions and administrations

Because DRUG_EXPOSURE records contain data that are stored in two separate source tables, prescriptions and administrations, a more elaborate logic is required to build valid records.

NOTE: This STEM table refers only to drugs and will use the STEM table logic with the addition of manual mappings to ensure relationships between the two tables can easily be referenced.

For clinical tables containing drug information, we will use the STEM table; however, the logic will differ from that described above for other clinical tables. Manual mappings will be included to ensure relationships between the source prescription table and drug look-up table are easily referenced.

• The STEM table will be used as an intermediate mapping table: the source data will first be mapped to the STEM table and the OMOP CDM tables will be populated with records from the STEM table.

• Manual mapping will be needed to link the source drug data (contained in prescriptions and administrations) to a drug look-up table (called drug_mapping_helper) and then to the STEM table.

• drug_mapping_helper only contains the ATC codes and their associated additional information. RxNorm builder will be used to create mappings to best level of granularity possible, aiming for Clinical Drug (or Clinical Drug Component) level to enable population of the drug_era and dose_era tables.

• The logic to link from the source data to drug_mapping_helper to the STEM table to the OMOP drug_exposure table will need to be done manually with a drug_key (will link prescriptions and drug_mapping_helper, epaspresbaseid will link administrations and prescriptions). The ETL will handle pump and non-pump drug data differently: drug_mapping_helper.numerator_value comes from administrations.value for non-pump drug data and from prescriptions.epaspresconc for pump drug data

• This slightly different STEM table logic will apply to the following table: DRUG_EXPOSURE.

A diagram of a drug exposure Description automatically generated